<?php
/* 
 * Author: Quan Van Sinh
 * Email: sinhvnb@vietnambiz.com
 */
class archives{

    function archives(){}

    function lastestArticle($limit=20){
        global $db;
        $sql = "SELECT title,link,image,short_description FROM article WHERE status='yes' ORDER BY create_date DESC LIMIT $limit";
        return $db->query($sql);
    }

    function getArticles(){
        global $db, $config;
        $create_date = $config['create_date'];

        $sqlTotal = "SELECT count(id) as numrows FROM article WHERE create_date LIKE '$create_date%' AND status='yes'";
        $rsTotalRows =  $db->query_first($sqlTotal);

        $totalRows =  $rsTotalRows['numrows'];
        $totalPages = ceil($totalRows/$config ['total_article_on_category']);
        $start = (($config ['page']*$config ['total_article_on_category'])-$config ['total_article_on_category']);
        $limit = $config ['total_article_on_category'];

        $sql = "SELECT title,link,image,short_description,create_date FROM article WHERE create_date LIKE '$create_date%' AND status='yes' ORDER BY create_date DESC LIMIT $start,$limit";
        $rsArticles = $db->query($sql);
        return array(
                    "totalRows" => $totalRows,
                    "rsArticles" => $rsArticles
                    );
    }

    function get_cat_id_to_arr($parent_id = 0){
        global $db;
        if(!$trees) $trees = array();
        $rsCat = $db->query("SELECT * FROM article_category WHERE  parent_id = ".intval($parent_id));
        if($rsCat){
            while($rs = $db->fetch_array($rsCat)){
                $trees[] = $rs['id'];
            }
        }
        return $trees;
    }


    function getHousingByCategory($limit){
        global $db; global $config;
        $sqlCheck = "SELECT parent_id FROM housing_category WHERE id=".intval($config['id']);
        $rsCheck=$db->query_first($sqlCheck);
        if($rsCheck['parent_id']!=0){
            $sqlTotal="SELECT id, title, price,price_unit,price_type,description
                        FROM housing h
                        WHERE
                        h.cat_id=".$config['id']." ORDER BY h.create_date DESC";

            $totalRows =  $db->num_rows($db->query($sqlTotal));
            $totalPages = ceil($totalRows/$config ['total_house_on_category']);
            $start = (($config ['page']*$config ['total_house_on_category'])-$config ['total_house_on_category']);

            $sql=   "SELECT id, title, price,price_unit,price_type,description
                    FROM housing h
                    WHERE
                    h.cat_id=".$config['id']." ORDER BY h.create_date DESC LIMIT $start,$limit";
            return Array("rs"=>$db->query($sql),"totalRows"=>$totalRows);
        }else{
            $sqlTotal="SELECT id, title, price,price_unit,price_type,description
                        FROM housing h ORDER BY h.create_date DESC";

            $totalRows =  $db->num_rows($db->query($sqlTotal));
            $totalPages = ceil($totalRows/$config ['total_house_on_category']);
            $start = (($config ['page']*$config ['total_house_on_category'])-$config ['total_house_on_category']);

            $sql=   "SELECT id, title, price,price_unit,price_type,description
                    FROM housing h ORDER BY h.create_date DESC LIMIT $start,$limit";
            return Array("rs"=>$db->query($sql),"totalRows"=>$totalRows);
        }
    }

    function getLastestArticlesByCategoryId($categoryId){
        global $db;
        $arrCatId = get_cat_id_to_arr($categoryId);
        $str_cat_id = '';
        for($i=0;$i<count($arrCatId);$i++){
            if($i==(count($arrCatId)-1)){
                $str_cat_id .= $arrCatId[$i];
            }else{
                $str_cat_id .= $arrCatId[$i].',';
            }
        }
        if($str_cat_id==''){
            $sql= "SELECT a.title,a.image,a.link,a.short_description,c.name as cat_name, c.link as cat_link FROM article a LEFT JOIN article_category c
                ON a.cat_id = c.id WHERE a.cat_id=".intval($categoryId)." AND a.status='yes' ORDER BY a.create_date DESC";
        }else{
            $sql= "SELECT a.title,a.image,a.link,a.short_description,c.name as cat_name, c.link as cat_link FROM article a LEFT JOIN article_category c
                ON a.cat_id = c.id WHERE a.cat_id=".intval($categoryId)." OR a.cat_id IN ($str_cat_id) AND a.status='yes' ORDER BY a.create_date DESC";
        }

        $rs = $db->query_first($sql);
        return $rs;
    }

    function getArticleByCategoryIdOffsetLimit($categoryId,$offset=0,$limit=10){
        global $db;
        $arrCatId = get_cat_id_to_arr($categoryId);
        $str_cat_id = '';
        for($i=0;$i<count($arrCatId);$i++){
            if($i==(count($arrCatId)-1)){
                $str_cat_id .= $arrCatId[$i];
            }else{
                $str_cat_id .= $arrCatId[$i].',';
            }
        }
        if($str_cat_id == ''){
            $sql= "SELECT id,title,link FROM article WHERE cat_id=".intval($categoryId)." AND status='yes' ORDER BY create_date DESC LIMIT $offset,$limit";
        }else{
            $sql= "SELECT id,title,link FROM article WHERE cat_id=".intval($categoryId)." OR cat_id IN ($str_cat_id) AND status='yes' ORDER BY create_date DESC LIMIT $offset,$limit";
        }

        $rs = $db->query($sql);
        return $rs;
    }

    function ortherOlderArticle($lastDate,$limit=10){
        global $db, $config;
        $lastDate = filter($lastDate);
        $create_date = $config['create_date'];
        $sql = "SELECT title,link,create_date FROM article WHERE create_date LIKE '$create_date%' AND create_date < '$lastDate' AND status='yes' ORDER BY create_date DESC LIMIT $limit";
        return $db->query($sql);
    }
}
?>
